package com.yellowpage.ndc.service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;




import java.sql.Connection;
import java.sql.SQLException;

import com.yellowpage.white.common.PageRender;
import com.yellowpage.white.dao.QueryAssistantSQL;
import com.yellowpage.white.service.BaseServiceImpl;
import com.yellowpage.ndc.data.ProdCustomer;
import com.yellowpage.ndc.dao.ProdCustomerDao;

public class ProdCustomerServiceImpl extends BaseServiceImpl implements
		ProdCustomerService {
	private ProdCustomerDao prodCustomerDao;

	public void setProdCustomerDao(ProdCustomerDao prodCustomerDao) {
		this.prodCustomerDao = prodCustomerDao;
	}

	/* Smooth generate Method */
	public ProdCustomer getProdCustomer(Long id) {
		return prodCustomerDao.get(id);
	}

	public List<ProdCustomer> getAllProdCustomer() {
		return prodCustomerDao.getAll();
	}

	public List<Map> queryProdCustomerByPage(Map queryMap, PageRender pageRender) {
		
		String whereStr = "";
		
		if(queryMap.get("custStatus")!=null){
			if(!queryMap.get("custStatus").toString().isEmpty()){
				String custStatus = queryMap.get("custStatus").toString();
				if(custStatus.equals("null")){
					whereStr+=" AND CUST_STATUS  IS NULL";
				}else{
					whereStr+=" AND CUST_STATUS  = "+custStatus;
				}
			}
		}
		
		String order = "";
		String orderlist= "";
		List orders = new ArrayList();
		if (!queryMap.get("ordername1").equals("")
				|| !queryMap.get("ordertel").equals("")
				|| !queryMap.get("orderareacode").equals("")
				|| !queryMap.get("orderregioncode").equals("")
				|| !queryMap.get("ordercitycode").equals("")
				|| !queryMap.get("orderaddr").equals("")) {
			if (queryMap.get("ordername1").equals("NAME1")) {
				orders.add(queryMap.get("ordername1").toString());
			}
			if (queryMap.get("orderareacode").equals("AREA_CODE")) {
				orders.add(queryMap.get("orderareacode").toString());
			}
			if (queryMap.get("ordertel").equals("TEL")) {
				orders.add(queryMap.get("ordertel").toString());
			}
			if (queryMap.get("orderregioncode").equals("REGION_CODE")) {
				orders.add(queryMap.get("orderregioncode").toString());
			}
			if (queryMap.get("ordercitycode").equals("CITY_CODE")) {
				orders.add(queryMap.get("ordercitycode").toString());
			}
			if (queryMap.get("orderaddr").equals("ADDR")) {
				orders.add(queryMap.get("orderaddr").toString());
			}
			
		}
		if (orders.size() != 0) {
			for (int i = 0; i < orders.size(); i++) {
				if (i == 0) {
					orderlist = orders.get(i).toString();
				} else {
					orderlist += "," + orders.get(i).toString();
				}
				order = "order by " + orderlist;
			}
		}
		System.out.print(order);

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"SELECT a.PROD_CLASS_CODE1 , a.PROD_CLASS_NAME1 , a.PROD_CLASS_CODE2 ," +
						" a.PROD_CLASS_NAME2 , a.CUST_ID , a.GROUP_ID , a.NAME1 , a.NAME2 , a.SUBNAME1 , " +
						"a.SUBNAME2 , a.SUBNAME3 , a.SUBNAME4 , a.AREA_CODE , a.TEL , a.TEL_EXT , a.FAX_FLAG ," +
						" a.SB , a.LIST_ORDER , a.REGION_CODE , a.REGION_NAME , a.CITY_CODE , a.CITY_NAME , " +
						"a.DESTRICT_CODE , a.DESTRICT_NAME  , a.ADDR , a.ZIP , a.PY_ADDR , " +
						"a.ENG_ADDR , a.POIX , a.POIY , a.CUST_STATUS , a.CUST_TYPE_CODE , a.CLASS_MAIN_ID , " +
						"a.ORG_ID , a.URL , a.WEIBO , a.WEIXIN , a.YIXIN , a.LOGO , a.SAFE_STATUS , a.BX_NAME , " +
						"a.PY_NAME , a.ENG_NAME , a.SP_NAME , a.SOURCE_ID , a.PROD_CLASS_ID , " +
						" date_format(a.MODIFY_TIME,'%Y-%m-%d') as MODIFY_TIME , a.MODIFY_EMP_ID ,"
						+ "(select display_name from CA_USER where user_id = a.MODIFY_EMP_ID) as MODIFY_EMP_CODE,"
						+ "(select display_name from CA_USER where user_id = a.INPUT_EMP_ID) as INPUT_EMP_CODE, " +
						" (select SP_NAME from db_ndc.prod_customer_uid as uj where uj.ID = a.ID) as UIDSPNAME ,"+
						" (select T9_NAME from db_ndc.prod_customer_uid as uj where uj.ID = a.ID) as UIDT9NAME ,"+
						"date_format(a.INPUT_TIME,'%Y-%m-%d') as INPUT_TIME , a.INPUT_EMP_ID , a.ID , a.SOURCE , case a.STATUS_114YP  when 2 then '正反' when 1 then '反向' else '不上' end AS STATUS_114YP ,case a.STATUS_360  when 2 then '正反' when 1 then '反向' else '不上' end AS STATUS_360   " +
						" FROM db_ndc.PROD_CUSTOMER a " +
						" WHERE 1=1 "+whereStr)
//						+ " and CUST_ID LIKE 'OF%' ")
				.addCondition(" and PROD_CLASS_CODE1 LIKE :prodClassCode1 ")
				.addCondition(" and PROD_CLASS_NAME1 LIKE :prodClassName1 ")
				.addCondition(" and PROD_CLASS_CODE2 LIKE :prodClassCode2 ")
				.addCondition(" and PROD_CLASS_NAME2 LIKE :prodClassName2 ")
				.addCondition(" and CUST_ID LIKE :custId ")
				.addCondition(" and GROUP_ID LIKE :groupId ")
				.addCondition(" and NAME1 LIKE :name1 ")
				.addCondition(" and NAME2 LIKE :name2 ")
				.addCondition(" and SUBNAME1 LIKE :subname1 ")
				.addCondition(" and SUBNAME2 LIKE :subname2 ")
				.addCondition(" and SUBNAME3 LIKE :subname3 ")
				.addCondition(" and SUBNAME4 LIKE :subname4 ")
				.addCondition(" and TEL LIKE :tel ")
				.addCondition(" and TEL_EXT LIKE :telExt ")
				.addCondition(" and FAX_FLAG LIKE :faxFlag ")
				.addCondition(" and SB LIKE :sb ")
				.addCondition(" and LIST_ORDER LIKE :listOrder ")
				.addCondition(" and REGION_CODE LIKE :regionCode ")
				.addCondition(" and REGION_NAME LIKE :regionName ")
				.addCondition(" and CITY_CODE LIKE :cityCode ")
				.addCondition(" and CITY_NAME LIKE :cityName ")
				.addCondition(" and DESTRICT_CODE LIKE :destrictCode ")
				.addCondition(" and DESTRICT_NAME LIKE :destrictName ")
				.addCondition(" and AREA_CODE LIKE :areaCode ")
				.addCondition(" and ADDR LIKE :addr ")
				.addCondition(" and ZIP LIKE :zip ")
				.addCondition(" and PY_ADDR LIKE :pyAddr ")
				.addCondition(" and ENG_ADDR LIKE :engAddr ")
				.addCondition(" and POIX LIKE :poix ")
				.addCondition(" and POIY LIKE :poiy ")
			//	.addCondition(" and CUST_STATUS LIKE :custStatus ")
				.addCondition(" and CUST_TYPE_CODE LIKE :custTypeCode ")
				.addCondition(" and CLASS_MAIN_ID LIKE :classMainId ")
				.addCondition(" and ORG_ID LIKE :orgId ")
				.addCondition(" and URL LIKE :url ")
				.addCondition(" and WEIBO LIKE :weibo ")
				.addCondition(" and WEIXIN LIKE :weixin ")
				.addCondition(" and YIXIN LIKE :yixin ")
				.addCondition(" and LOGO LIKE :logo ")
				.addCondition(" and SAFE_STATUS LIKE :safeStatus ")
				.addCondition(" and BX_NAME LIKE :bxName ")
				.addCondition(" and PY_NAME LIKE :pyName ")
				.addCondition(" and ENG_NAME LIKE :engName ")
				.addCondition(" and SP_NAME LIKE :spName ")
				.addCondition(" and SOURCE_ID LIKE :sourceId ")
				.addCondition(" and PROD_CLASS_ID LIKE :prodClassId ")
				.addCondition(" and date_format(INPUT_TIME,'%Y-%m-%d') =  :inputTime  ")
				.addCondition(" and date_format(MODIFY_TIME,'%Y-%m-%d') =  :modifyTime  ")
				.addCondition(" and MODIFY_EMP_ID LIKE (select USER_ID from CA_USER where DISPLAY_NAME = :modifyEmpCode ) ")
				.addCondition(" and INPUT_EMP_ID LIKE (select USER_ID from CA_USER where DISPLAY_NAME = :inputEmpCode ) ")
				.addCondition(" and SOURCE LIKE :source ")
				.addCondition(" and STATUS_114YP = :status114yp ")
				.addCondition(" and STATUS_360 = :status360 "); 
		sqlAssistant.setTail(order); 
		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender, true);

		return resultList;
	}
	
	

	
	public List<Map> queryProdCustomerBDCByPage(Map queryMap, PageRender pageRender){
		
		
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"SELECT a.PROD_CLASS_CODE1 , a.PROD_CLASS_NAME1 , a.PROD_CLASS_CODE2 ," +
						" a.PROD_CLASS_NAME2 , a.CUST_ID , a.GROUP_ID , a.NAME1 , a.NAME2 , a.TEL , a.TEL_EXT  ," +
						" a.LIST_ORDER , a.REGION_CODE , a.REGION_NAME , a.CITY_CODE , a.CITY_NAME , " +
						"a.DESTRICT_CODE , a.DESTRICT_NAME  ,a.AREA_CODE, a.ADDR , a.ZIP  , " +
						"a.ENG_ADDR , a.POIX , a.POIY , a.CUST_STATUS  , " +
						"a.ORG_ID , a.URL , a.WEIBO , a.WEIXIN , a.YIXIN , a.LOGO , a.SOURCE , a.OLD_TEL , " +
						"a.ADDR_ID , a.SEQ_ID  " +
						" FROM db_ndc.prod_customer_20141209 a " +
						" WHERE 1=1 ")
//						+ " and CUST_ID LIKE 'OF%' ")
				.addCondition(" and PROD_CLASS_CODE1 LIKE :prodClassCode1 ")
				.addCondition(" and PROD_CLASS_NAME1 LIKE :prodClassName1 ")
				.addCondition(" and PROD_CLASS_CODE2 LIKE :prodClassCode2 ")
				.addCondition(" and PROD_CLASS_NAME2 LIKE :prodClassName2 ")
				.addCondition(" and CUST_ID LIKE :custId ")
				.addCondition(" and GROUP_ID LIKE :groupId ")
				.addCondition(" and NAME1 LIKE :name1 ")
				.addCondition(" and NAME2 LIKE :name2 ")
				.addCondition(" and TEL LIKE :tel ")
				.addCondition(" and TEL_EXT LIKE :telExt ")
				.addCondition(" and LIST_ORDER LIKE :listOrder ")
				.addCondition(" and REGION_CODE LIKE :regionCode ")
				.addCondition(" and REGION_NAME LIKE :regionName ")
				.addCondition(" and CITY_CODE LIKE :cityCode ")
				.addCondition(" and CITY_NAME LIKE :cityName ")
				.addCondition(" and DESTRICT_CODE LIKE :destrictCode ")
				.addCondition(" and DESTRICT_NAME LIKE :destrictName ")
				.addCondition(" and AREA_CODE LIKE :areaCode ")
				.addCondition(" and ADDR LIKE :addr ")
				.addCondition(" and ZIP LIKE :zip ")
				.addCondition(" and ENG_ADDR LIKE :engAddr ")
				.addCondition(" and POIX LIKE :poix ")
				.addCondition(" and POIY LIKE :poiy ")
				.addCondition(" and CUST_STATUS LIKE :custStatus ")
				.addCondition(" and ORG_ID LIKE :orgId ")
				.addCondition(" and URL LIKE :url ")
				.addCondition(" and WEIBO LIKE :weibo ")
				.addCondition(" and WEIXIN LIKE :weixin ")
				.addCondition(" and YIXIN LIKE :yixin ")
				.addCondition(" and LOGO LIKE :logo ")
				.addCondition(" and SOURCE LIKE :safeStatus ")
				.addCondition(" and OLD_TEL LIKE :oldTel ")
				.addCondition(" and ADDR_ID LIKE :addrId ")
				.addCondition(" and SEQ_ID LIKE :seqId ")
				; 

		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender, true);

		return resultList;
		
		
	}
	
	//工作量统计
	public List<Map> queryProdWorkload(Map queryMap, PageRender pageRender){
		String customerwherrSql="";
		String groupwherrSql="";
		if(queryMap.get("startTime")!=null){
			if(!queryMap.get("startTime").equals("")){
				customerwherrSql+=" AND date_format(c.CREATE_TIME,'%Y-%m-%d')>= '"+queryMap.get("startTime")+"'";
				groupwherrSql+=" AND date_format(g.CREATE_TIME,'%Y-%m-%d')>= '"+queryMap.get("startTime")+"'";
			}
		}
		if(queryMap.get("endTime")!=null){
			if(!queryMap.get("endTime").equals("")){
				customerwherrSql+=" AND date_format(c.CREATE_TIME,'%Y-%m-%d')<= '"+queryMap.get("endTime")+"'";
				groupwherrSql+=" AND date_format(g.CREATE_TIME,'%Y-%m-%d')<= '"+queryMap.get("endTime")+"'";
			}
		}
		/*QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"SELECT a.display_name as operator,(select count(1) from db_ndc.PROD_CUSTOMER where MODIFY_EMP_ID = a.user_id "+wherrSql+") as oper_customer ,"
						+ "(select count(1)  from db_ndc.PROD_GROUP where MODIFY_EMP_ID = a.user_id "+wherrSql+") as oper_group  FROM CA_USER a " +
						" WHERE 1=1 ")
//						+ " and CUST_ID LIKE 'OF%' ")
				.addCondition(" and a.display_name LIKE :operator ");
		*/
		
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"SELECT u.`NAME`,u.DISPLAY_NAME,"
						+"(SELECT COUNT(1) FROM NDC_PROD_CUSTOMER_HIS AS c WHERE c.CREATE_USER = u.USER_ID AND c.TYPE = '0' "+customerwherrSql+") AS CADDCOUNT ," 
						+"(SELECT COUNT(1) FROM NDC_PROD_CUSTOMER_HIS AS c WHERE c.CREATE_USER = u.USER_ID AND c.TYPE = '1' "+customerwherrSql+") AS CUPDATECOUNT ,"
						+"(SELECT COUNT(1) FROM NDC_PROD_CUSTOMER_HIS AS c WHERE c.CREATE_USER = u.USER_ID AND c.TYPE = '2' "+customerwherrSql+") AS CDELETECOUNT ,"
						+"(SELECT COUNT(1) FROM NDC_PROD_CUSTOMER_HIS AS c WHERE c.CREATE_USER = u.USER_ID AND c.TYPE = '3' "+customerwherrSql+") AS C360COUNT ,"
						+"(SELECT COUNT(1) FROM NDC_PROD_CUSTOMER_HIS AS c WHERE c.CREATE_USER = u.USER_ID AND c.TYPE = '4' "+customerwherrSql+") AS CDELFALSECOUNT ,"
						+"(SELECT COUNT(1) FROM NDC_PROD_GROUP_HIS AS g WHERE g.CREATE_USER = u.USER_ID AND g.TYPE = '0' "+groupwherrSql+") AS GADDCOUNT ,"
						+"(SELECT COUNT(1) FROM NDC_PROD_GROUP_HIS AS g WHERE g.CREATE_USER = u.USER_ID AND g.TYPE = '1' "+groupwherrSql+") AS GUPDATECOUNT ,"
						+"(SELECT COUNT(1) FROM NDC_PROD_GROUP_HIS AS g WHERE g.CREATE_USER = u.USER_ID AND g.TYPE = '2' "+groupwherrSql+") AS GDELETECOUNT "
						+"FROM CA_USER AS u "
						+" WHERE 1=1 ")
				.addCondition(" and u.DISPLAY_NAME LIKE :operator ")
				.addCondition(" and u.`NAME` LIKE :userName ");
		
		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender, false);

		return resultList;
	}
	
	public List<Map> countProdCustomer(Map queryMap) {	
		
		String whereStr = "";
		
		if(queryMap.get("custStatus")!=null){
			if(!queryMap.get("custStatus").toString().isEmpty()){
				String custStatus = queryMap.get("custStatus").toString();
				if(custStatus.equals("null")){
					whereStr+=" AND CUST_STATUS  IS NULL";
				}else{
					whereStr+=" AND CUST_STATUS  = "+custStatus;
				}
			}
		}
        
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(
				"SELECT COUNT(1) AS 'TOTAL'  FROM db_ndc.PROD_CUSTOMER a where 1=1 "+whereStr)
				    			.addCondition(" and PROD_CLASS_CODE1 LIKE :prodClassCode1 ")
				.addCondition(" and PROD_CLASS_NAME1 LIKE :prodClassName1 ")
				.addCondition(" and PROD_CLASS_CODE2 LIKE :prodClassCode2 ")
				.addCondition(" and PROD_CLASS_NAME2 LIKE :prodClassName2 ")
				.addCondition(" and CUST_ID LIKE :custId ")
				.addCondition(" and GROUP_ID LIKE :groupId ")
				.addCondition(" and NAME1 LIKE :name1 ")
				.addCondition(" and NAME2 LIKE :name2 ")
				.addCondition(" and SUBNAME1 LIKE :subname1 ")
				.addCondition(" and SUBNAME2 LIKE :subname2 ")
				.addCondition(" and SUBNAME3 LIKE :subname3 ")
				.addCondition(" and SUBNAME4 LIKE :subname4 ")
				.addCondition(" and TEL LIKE :tel ")
				.addCondition(" and TEL_EXT LIKE :telExt ")
				.addCondition(" and FAX_FLAG LIKE :faxFlag ")
				.addCondition(" and SB LIKE :sb ")
				.addCondition(" and LIST_ORDER LIKE :listOrder ")
				.addCondition(" and REGION_CODE LIKE :regionCode ")
				.addCondition(" and REGION_NAME LIKE :regionName ")
				.addCondition(" and CITY_CODE LIKE :cityCode ")
				.addCondition(" and CITY_NAME LIKE :cityName ")
				.addCondition(" and DESTRICT_CODE LIKE :destrictCode ")
				.addCondition(" and DESTRICT_NAME LIKE :destrictName ")
				.addCondition(" and AREA_CODE LIKE :areaCode ")
				.addCondition(" and ADDR LIKE :addr ")
				.addCondition(" and ZIP LIKE :zip ")
				.addCondition(" and PY_ADDR LIKE :pyAddr ")
				.addCondition(" and ENG_ADDR LIKE :engAddr ")
				.addCondition(" and POIX LIKE :poix ")
				.addCondition(" and POIY LIKE :poiy ")
				//.addCondition(" and CUST_STATUS LIKE :custStatus ")
				.addCondition(" and CUST_TYPE_CODE LIKE :custTypeCode ")
				.addCondition(" and CLASS_MAIN_ID LIKE :classMainId ")
				.addCondition(" and ORG_ID LIKE :orgId ")
				.addCondition(" and URL LIKE :url ")
				.addCondition(" and WEIBO LIKE :weibo ")
				.addCondition(" and WEIXIN LIKE :weixin ")
				.addCondition(" and YIXIN LIKE :yixin ")
				.addCondition(" and LOGO LIKE :logo ")
				.addCondition(" and SAFE_STATUS LIKE :safeStatus ")
				.addCondition(" and BX_NAME LIKE :bxName ")
				.addCondition(" and PY_NAME LIKE :pyName ")
				.addCondition(" and ENG_NAME LIKE :engName ")
				.addCondition(" and SP_NAME LIKE :spName ")
				.addCondition(" and SOURCE_ID LIKE :sourceId ")
				.addCondition(" and PROD_CLASS_ID LIKE :prodClassId ")
				.addCondition(" and date_format(INPUT_TIME,'%Y-%m-%d') =  :inputTime  ")
				.addCondition(" and date_format(MODIFY_TIME,'%Y-%m-%d') =  :modifyTime  ")
				.addCondition(" and MODIFY_EMP_ID LIKE (select USER_ID from CA_USER where DISPLAY_NAME = :modifyEmpCode ) ")
				.addCondition(" and INPUT_EMP_ID LIKE (select USER_ID from CA_USER where DISPLAY_NAME = :inputEmpCode ) ")
				.addCondition(" and SOURCE LIKE :source ")
				.addCondition(" and STATUS_114YP = :status114yp ")
				.addCondition(" and STATUS_360 = :status360 ");  
							    ; 		    
		
		List<Map> resultList = sqlAssistant.query(
				baseDao.getConnection(), queryMap);

		return resultList;
	}
	
	 public List<Map> ProdCustomerexcel(Map queryMap) {		
	        
		 QueryAssistantSQL sqlAssistant = baseDao
					.newQueryAssistantSQL(
							"SELECT a.PROD_CLASS_CODE1 , a.PROD_CLASS_NAME1 , a.PROD_CLASS_CODE2 , a.PROD_CLASS_NAME2 , a.CUST_ID , a.GROUP_ID , a.NAME1 , a.NAME2 , a.SUBNAME1 , a.SUBNAME2 , a.SUBNAME3 , a.SUBNAME4 , a.AREA_CODE , a.TEL , a.TEL_EXT , a.FAX_FLAG , a.SB , a.LIST_ORDER , a.REGION_CODE , a.REGION_NAME , a.CITY_CODE , a.CITY_NAME , a.DESTRICT_CODE , a.DESTRICT_NAME , a.AREA_CODE , a.ADDR , a.ZIP , a.PY_ADDR , a.ENG_ADDR , a.POIX , a.POIY , a.CUST_STATUS , a.CUST_TYPE_CODE , a.CLASS_MAIN_ID , a.ORG_ID , a.URL , a.WEIBO , a.WEIXIN , a.YIXIN , a.LOGO , a.SAFE_STATUS , a.BX_NAME , a.PY_NAME , a.ENG_NAME , a.SP_NAME , a.SOURCE_ID , a.PROD_CLASS_ID , " +
							" date_format(a.MODIFY_TIME,'%Y-%m-%d') as MODIFY_TIME , a.MODIFY_EMP_ID , date_format(a.INPUT_TIME,'%Y-%m-%d') as INPUT_TIME , a.INPUT_EMP_ID , a.ID , a.SOURCE , case a.STATUS_114YP  when 2 then '正反' when 1 then '方向' else '不上' end AS STATUS_114YP ,case a.STATUS_360  when 2 then '正反' when 1 then '方向' else '不上' end AS STATUS_360   " +
							" FROM db_ndc.PROD_CUSTOMER a " +
							" WHERE 1=1 ")
//							+ " and CUST_ID LIKE 'OF%' ")
					.addCondition(" and PROD_CLASS_CODE1 LIKE :prodClassCode1 ")
					.addCondition(" and PROD_CLASS_NAME1 LIKE :prodClassName1 ")
					.addCondition(" and PROD_CLASS_CODE2 LIKE :prodClassCode2 ")
					.addCondition(" and PROD_CLASS_NAME2 LIKE :prodClassName2 ")
					.addCondition(" and CUST_ID LIKE :custId ")
					.addCondition(" and GROUP_ID LIKE :groupId ")
					.addCondition(" and NAME1 LIKE :name1 ")
					.addCondition(" and NAME2 LIKE :name2 ")
					.addCondition(" and SUBNAME1 LIKE :subname1 ")
					.addCondition(" and SUBNAME2 LIKE :subname2 ")
					.addCondition(" and SUBNAME3 LIKE :subname3 ")
					.addCondition(" and SUBNAME4 LIKE :subname4 ")
					.addCondition(" and TEL LIKE :tel ")
					.addCondition(" and TEL_EXT LIKE :telExt ")
					.addCondition(" and FAX_FLAG LIKE :faxFlag ")
					.addCondition(" and SB LIKE :sb ")
					.addCondition(" and LIST_ORDER LIKE :listOrder ")
					.addCondition(" and REGION_CODE LIKE :regionCode ")
					.addCondition(" and REGION_NAME LIKE :regionName ")
					.addCondition(" and CITY_CODE LIKE :cityCode ")
					.addCondition(" and CITY_NAME LIKE :cityName ")
					.addCondition(" and DESTRICT_CODE LIKE :destrictCode ")
					.addCondition(" and DESTRICT_NAME LIKE :destrictName ")
					.addCondition(" and AREA_CODE LIKE :areaCode ")
					.addCondition(" and ADDR LIKE :addr ")
					.addCondition(" and ZIP LIKE :zip ")
					.addCondition(" and PY_ADDR LIKE :pyAddr ")
					.addCondition(" and ENG_ADDR LIKE :engAddr ")
					.addCondition(" and POIX LIKE :poix ")
					.addCondition(" and POIY LIKE :poiy ")
					.addCondition(" and CUST_STATUS LIKE :custStatus ")
					.addCondition(" and CUST_TYPE_CODE LIKE :custTypeCode ")
					.addCondition(" and CLASS_MAIN_ID LIKE :classMainId ")
					.addCondition(" and ORG_ID LIKE :orgId ")
					.addCondition(" and URL LIKE :url ")
					.addCondition(" and WEIBO LIKE :weibo ")
					.addCondition(" and WEIXIN LIKE :weixin ")
					.addCondition(" and YIXIN LIKE :yixin ")
					.addCondition(" and LOGO LIKE :logo ")
					.addCondition(" and SAFE_STATUS LIKE :safeStatus ")
					.addCondition(" and BX_NAME LIKE :bxName ")
					.addCondition(" and PY_NAME LIKE :pyName ")
					.addCondition(" and ENG_NAME LIKE :engName ")
					.addCondition(" and SP_NAME LIKE :spName ")
					.addCondition(" and SOURCE_ID LIKE :sourceId ")
					.addCondition(" and PROD_CLASS_ID LIKE :prodClassId ")
					.addCondition(" and date_format(INPUT_TIME,'%Y-%m-%d') =  :inputTime  ")
					.addCondition(" and date_format(MODIFY_TIME,'%Y-%m-%d') =  :modifyTime  ")
					.addCondition(" and MODIFY_EMP_ID LIKE (select USER_ID from CA_USER where DISPLAY_NAME = :modifyEmpCode ) ")
					.addCondition(" and INPUT_EMP_ID LIKE (select USER_ID from CA_USER where DISPLAY_NAME = :inputEmpCode ) ")
					.addCondition(" and SOURCE LIKE :source ")
					.addCondition(" and STATUS_114YP = :status114yp ")
					.addCondition(" and STATUS_360 = :status360 ")
					;     
								    	    
			
			List<Map> resultList = sqlAssistant.query(
					baseDao.getConnection(), queryMap);

			return resultList;
		}
	
	public String getCustId(){
		log.error("getCustId in...................");
    	String source = "";
    	
    	QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(" select max(right(pc.CUST_ID,8)) as MaxSeq from db_ndc.PROD_CUSTOMER pc where pc.CUST_ID LIKE 'OF%' "); 	
         
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(), new HashMap());

		for(int i = 0; i < resultList.size(); i++){
			source = resultList.get(i).get("MAXSEQ").toString();
		}
		log.error("source is " + source);
		return source;
    }
	
	public boolean getGroupIdExists(String groupId){
		log.error("getGroupIdExists in...................");
		boolean flag = false;
//		log.error("传进来 groupId is ..................." + groupId);
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(" select 1 from db_ndc.PROD_GROUP pg where pg.GROUP_ID = '" + groupId + "'");
		
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(), new HashMap());
		
		if(resultList.size() > 0){
			flag = true;
		}
		
		return flag;
	}

	public void saveProdCustomer(ProdCustomer prodCustomer) {
		prodCustomerDao.save(prodCustomer);
	}

	public void updateProdCustomer(ProdCustomer prodCustomer) {
		prodCustomerDao.update(prodCustomer);
	}

	public void delete(List deleteList) {
		prodCustomerDao.remove(deleteList);
	}
	
	public List<Map> getProdCustomerByGroupId(String groupId){
		
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(" select * from db_ndc.PROD_CUSTOMER pg where pg.GROUP_ID = '" + groupId + "'");
		
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(), new HashMap());
		return resultList;
	}
	
	public String getIdBycustId(String custId){
		
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(" select  c.ID from db_ndc.PROD_CUSTOMER c where c.CUST_ID = '" + custId + "'");
		
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(), new HashMap());
		if(resultList.size()>0){
			return resultList.get(0).get("ID").toString();
		}else{
			return "";
		}
		
		
		
	}
	
	//清空groupid
	public void emptyGroupid(String groupId){
		
	
		
		String sql = " update db_ndc.PROD_CUSTOMER set GROUP_ID='' where GROUP_ID = '" + groupId + "'";
		Connection connection = baseDao.getConnection();
		
		QueryAssistantSQL updateParterSql = baseDao
				.newQueryAssistantSQL(sql);
		try {
			updateParterSql.updateSql(baseDao.getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
	}

}